<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Class	: M_hitungpresensi
 * 
 * Table	: hitungpresensi
 *  
 * @author masongbee
 *
 */
class M_hitungpresensi extends CI_Model{

	function __construct(){
		parent::__construct();		
	}
	
	/**
	 * Fungsi	: getAll
	 * 
	 * Untuk mengambil all-data
	 * 
	 * @param number $start
	 * @param number $page
	 * @param number $limit
	 * @return json
	 */
	 
	function ProcessInit(){
		// --------------------- Proses 1 Inisialisasi HitungPresensi
		
	}
	
	public function getProsesHitpres(){
		$totalData = $this->db->get_where('init',array('PARAMETER' => 'Total Data Hit'))->result();
		$totalProses = $this->db->get_where('init',array('PARAMETER' => 'CounterHit'))->result();
		$json	= array(
			'success'   => TRUE,
			'message'   => 'Total Data...',
			'totalData' => $totalData[0]->VALUE,
			'totalProses' => $totalProses[0]->VALUE
		);
		
		return $json;
	}
	
	function ProcessUpdate($tglmulai,$tglsampai){
		$cnt = 0;
		$this->db->where(array('PARAMETER' => 'Total Data Hit'))->update('init', array('VALUE'=>$cnt));
		$this->db->where(array('PARAMETER' => 'CounterHit'))->update('init', array('VALUE'=>$cnt));
		// --------------------- Proses 2 Update Untuk JamKerja Bersih setelah dipotong MakanSiang / MakanMalam
		/*$sql1 = "UPDATE hitungpresensi t1
		JOIN (
			SELECT t2.nik, DATE(t2.tjmasuk) as TANGGAL, MIN(t2.TJMASUK) AS TJMASUK, MAX(t2.TJKELUAR) AS TJKELUAR, SUM(TIMESTAMPDIFF(MINUTE,t2.TJMASUK,t2.TJKELUAR)) AS JAMKERJA,
			IF((
			IF(TIMESTAMP(MAX(t2.TJKELUAR)) < TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t2.TJKELUAR)) > TIMESTAMP(DATE(t2.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t2.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t2.TJKELUAR))> TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'))AND(TIMESTAMP(MAX(t2.TJKELUAR))< TIMESTAMP(DATE(t2.TJMASUK),'18:00:00')),60,0)))=0,
			IF(TIMESTAMP(MAX(t2.TJKELUAR)) < TIMESTAMP(DATE(t2.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t2.TJKELUAR)) > TIMESTAMP(DATE(t2.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t2.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t2.TJKELUAR))> TIMESTAMP(DATE(t2.TJMASUK),'18:30:00'),30,0)),
			IF(TIMESTAMP(MAX(t2.TJKELUAR)) < TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t2.TJKELUAR)) > TIMESTAMP(DATE(t2.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t2.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t2.TJKELUAR))> TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'),60,0))) AS POTONGMAKAN,
			(SUM(TIMESTAMPDIFF(MINUTE,t2.TJMASUK,t2.TJKELUAR)) - (IF((
			IF(TIMESTAMP(MAX(t2.TJKELUAR)) < TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t2.TJKELUAR)) > TIMESTAMP(DATE(t2.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t2.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t2.TJKELUAR))> TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'))AND(TIME(t2.TJKELUAR)< TIMESTAMP(DATE(t2.TJMASUK),'18:00:00')),60,0)))=0,
			IF(TIMESTAMP(MAX(t2.TJKELUAR)) < TIMESTAMP(DATE(t2.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t2.TJKELUAR)) > TIMESTAMP(DATE(t2.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t2.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t2.TJKELUAR))> TIMESTAMP(DATE(t2.TJMASUK),'18:30:00'),30,0)),
			IF(TIMESTAMP(MAX(t2.TJKELUAR)) < TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t2.TJKELUAR)) > TIMESTAMP(DATE(t2.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t2.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t2.TJKELUAR))> TIMESTAMP(DATE(t2.TJMASUK),'13:00:00'),60,0))))) AS JAMBERSIH
			FROM presensi t2
			WHERE t2.TJMASUK >= DATE('$tglmulai') and t2.TJKELUAR <= DATE('$tglsampai')
			GROUP BY t2.NIK, DATE(t2.TJMASUK)) AS t3
		ON t1.NIK=t3.nik AND t1.TANGGAL=t3.TANGGAL
		SET 
			t1.JENISABSEN=IF((IF(t3.JAMBERSIH > 0,1,0)) = 1,'HD','AL'),
			t1.HARIKERJA = IF(t3.JAMBERSIH > 0,1,0),
			t1.JAMKERJA = t3.JAMBERSIH,
			t1.JAMLEMBUR = 0,
			t1.JAMKURANG = 0,
			t1.JAMBOLOS = 0,
			t1.EXTRADAY = 0,
			t1.TERLAMBAT = 'T',
			t1.PLGLBHAWAL = 'T',
			t1.JENISLEMBUR = null
			;";
		$query1 = $this->db->query($sql1);
		
		// ----------------- 04-07-2013 Proses 3 Update untuk JAMKERJA, JAMLEMBUR, JENISLEMBUR, EXTRADAY -----------------
		$sql2 = "UPDATE hitungpresensi t5
		JOIN (
			SELECT DATE(t1.TJMASUK) as TANGGAL, t1.NIK, t1.TJMASUK, MAX(t1.TJKELUAR) as JAMKELUAR,t4.TJMASUK as TJLEMBUR, SUM(TIMESTAMPDIFF(MINUTE,t1.TJMASUK,t4.TJMASUK)) as JAMKERJA, 
			SUM(TIMESTAMPDIFF(MINUTE,t4.TJMASUK,t1.TJKELUAR)) as LEMBURKOTOR,
			IF((
			IF(TIMESTAMP(MAX(t1.TJKELUAR)) < TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t1.TJKELUAR)) > TIMESTAMP(DATE(t4.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t1.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t1.TJKELUAR))> TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'))AND(TIMESTAMP(MAX(t1.TJKELUAR))< TIMESTAMP(DATE(t4.TJMASUK),'18:00:00')),60,0)))=0,
			IF(TIMESTAMP(MAX(t1.TJKELUAR)) < TIMESTAMP(DATE(t4.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t1.TJKELUAR)) > TIMESTAMP(DATE(t4.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t1.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t1.TJKELUAR))> TIMESTAMP(DATE(t4.TJMASUK),'18:30:00'),30,0)),
			IF(TIMESTAMP(MAX(t1.TJKELUAR)) < TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t1.TJKELUAR)) > TIMESTAMP(DATE(t4.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t1.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t1.TJKELUAR))> TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'),60,0))) AS POTONGMAKAN,
			(SUM(TIMESTAMPDIFF(MINUTE,t4.TJMASUK,t1.TJKELUAR)) - (IF((
			IF(TIMESTAMP(MAX(t1.TJKELUAR)) < TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t1.TJKELUAR)) > TIMESTAMP(DATE(t4.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t1.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t1.TJKELUAR))> TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'))AND(TIME(t1.TJKELUAR)< TIMESTAMP(DATE(t4.TJMASUK),'18:00:00')),60,0)))=0,
			IF(TIMESTAMP(MAX(t1.TJKELUAR)) < TIMESTAMP(DATE(t4.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t1.TJKELUAR)) > TIMESTAMP(DATE(t4.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t1.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t1.TJKELUAR))> TIMESTAMP(DATE(t4.TJMASUK),'18:30:00'),30,0)),
			IF(TIMESTAMP(MAX(t1.TJKELUAR)) < TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t1.TJKELUAR)) > TIMESTAMP(DATE(t4.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t1.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t1.TJKELUAR))> TIMESTAMP(DATE(t4.TJMASUK),'13:00:00'),60,0))))) AS JAMLEMBUR,
			t4.JENISLEMBUR,
			IF(t4.JENISLEMBUR = 'B',0,1) AS EXTRADAY,
			SUM(TIMESTAMPDIFF(MINUTE,t1.TJMASUK,t1.TJKELUAR)) as TOTAL
			FROM presensi t1
				JOIN (
				SELECT t3.NIK, t2.NOLEMBUR, t2.TANGGAL, t3.TJMASUK, t3.JENISLEMBUR
				FROM splembur t2
				RIGHT JOIN rencanalembur t3
				ON t2.NOLEMBUR = t3.NOLEMBUR AND DATE(t3.TJMASUK)>=DATE('$tglmulai') AND DATE(t3.TJMASUK)<=DATE('$tglsampai') ) as t4
			ON t1.nik=t4.NIK AND date(t1.TJMASUK)=DATE(t4.TJMASUK)
			GROUP BY t1.NIK,DATE(t1.TJMASUK)) as t6
			ON t5.NIK=t6.NIK AND t5.TANGGAL=t6.TANGGAL
		SET
			t5.JENISABSEN=IF((IF(t6.JAMKERJA > 0,1,0)) = 1,'HD','AL'),
			t5.JAMKERJA=(t6.JAMKERJA/60),
			t5.JAMLEMBUR=(t6.JAMLEMBUR/60),
			t5.JENISLEMBUR=t6.JENISLEMBUR,
			t5.EXTRADAY=t6.EXTRADAY;";
		$query2 = $this->db->query($sql2);

		// -----------------------------------------------------
		*/
		// ----------------- 05-07-2013 Proses 4 Update untuk JENISABSEN, HARIKERJA, JAMKURANG, TERLAMBAT, PLGLBHAWAL -----------------
		/*$sql3 = "UPDATE hitungpresensi t9
		JOIN (
		SELECT t8.KODESHIFT,t8.NAMASHIFT,t8.SHIFTKE,t8.JAMDARI,t8.JAMSAMPAI,t8.TOTALJAM,t7.NIK,DATE(MIN(t7.TJMASUK))AS TANGGAL,t8.JENISHARI,MIN(t7.TJMASUK)AS JAMMASUK,MAX(t7.TJKELUAR)AS JAMKELUAR,
		SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR)) AS JAMKERJA,
		IF((
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'))AND(TIMESTAMP(MAX(t7.TJKELUAR))< TIMESTAMP(DATE(t7.TJMASUK),'18:00:00')),60,0)))=0,
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),30,0)),
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),60,0))) AS POTONGMAKAN,
		(SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR)) - (IF((
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'))AND(TIME(t7.TJKELUAR)< TIMESTAMP(DATE(t7.TJMASUK),'18:00:00')),60,0)))=0,
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),30,0)),
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),60,0))))) AS JAMBERSIH,
		IF((t8.TOTALJAM-(SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR)) - (IF((
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'))AND(TIME(t7.TJKELUAR)< TIMESTAMP(DATE(t7.TJMASUK),'18:00:00')),60,0)))=0,
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),30,0)),
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),60,0)))))) >=0,(t8.TOTALJAM-(SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR)) - (IF((
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF((TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'))AND(TIME(t7.TJKELUAR)< TIMESTAMP(DATE(t7.TJMASUK),'18:00:00')),60,0)))=0,
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'18:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'18:30:00'),30,0)),
		IF(TIMESTAMP(MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),IF(TIMESTAMP(MAX(t7.TJKELUAR)) > TIMESTAMP(DATE(t7.TJMASUK),'12:00:00'),TIME_FORMAT(TIMESTAMP(MAX(t7.TJKELUAR)),'%i'),0),IF(TIMESTAMP(MAX(t7.TJKELUAR))> TIMESTAMP(DATE(t7.TJMASUK),'13:00:00'),60,0)))))),0) AS JAMKURANG,
		IF((MIN(t7.TJMASUK)) > TIMESTAMP(DATE(t7.TJMASUK),t8.JAMDARI),'Y','T') as TERLAMBAT,
		IF((MAX(t7.TJKELUAR)) < TIMESTAMP(DATE(t7.TJMASUK),t8.JAMSAMPAI),'Y','T') as PLGLBHAWAL
		FROM presensi t7
		JOIN (
			SELECT t5.VALIDTO, t6.KODESHIFT, t6.NIK, t5.NAMASHIFT, t5.SHIFTKE,t5.JENISHARI, t5.JAMDARI, t5.JAMSAMPAI, ((HOUR(TIMEDIFF(t5.JAMDARI,t5.JAMSAMPAI))*60) + MINUTE(TIMEDIFF(t5.JAMDARI,t5.JAMSAMPAI)) - 60) as TOTALJAM, t5.TGLMULAI, t5.TGLSAMPAI
			FROM karyawanshift t6
			JOIN (
				SELECT t4.VALIDTO, t4.KODESHIFT, t1.NAMASHIFT, t1.SHIFTKE, t1.JENISHARI, t1.JAMDARI, t1.JAMSAMPAI, t4.TGLMULAI,t4.TGLSAMPAI
				FROM shiftjamkerja t1
				JOIN (
				SELECT t3.KODESHIFT, t2.NAMASHIFT, t3.SHIFTKE, t2.VALIDTO, t3.TGLMULAI, t3.TGLSAMPAI
				FROM shift t2
				RIGHT JOIN pembagianshift t3
				ON t2.NAMASHIFT=t3.NAMASHIFT) as t4
				ON t1.NAMASHIFT=t4.namashift AND t1.SHIFTKE=t4.SHIFTKE) as t5
			ON t5.KODESHIFT = t6.KODESHIFT ) as t8
		ON t7.NIK=t8.NIK AND DATE(t7.TJMASUK) <= DATE(t8.TGLSAMPAI) AND DATE(t7.TJMASUK) >= DATE(t8.TGLMULAI) AND (t8.JENISHARI = (IF(DAYNAME(DATE(t7.TJMASUK)) = 'Friday','J','N')))
		GROUP BY t7.NIK,DATE(t7.TJMASUK), t8.JENISHARI) AS t10
		ON t9.NIK=t10.NIK AND t9.TANGGAL=t10.TANGGAL AND DATE(t10.TANGGAL)>=DATE('$tglmulai') AND DATE(t10.TANGGAL)<=DATE('$tglsampai')
		SET
			t9.JENISABSEN=IF((IF(t10.JAMBERSIH > 0,1,0)) = 1,'HD','AL'),
			T9.JAMBOLOS=IF(T9.JENISABSEN = 'AL',480,0),
			t9.HARIKERJA=IF(t10.JAMBERSIH > 0,1,0),
			t9.JAMKERJA=t10.JAMBERSIH,
			t9.JAMKURANG=t10.JAMKURANG,
			t9.TERLAMBAT=t10.TERLAMBAT,
			t9.PLGLBHAWAL=t10.PLGLBHAWAL;";
			
			
		* ------------------------------- 21-07-2013 Revisi JamRehat pada shiftjamkerja --------------------------
		SELECT t8.KODESHIFT,t8.NAMASHIFT,t8.SHIFTKE,t8.JAMDARI,t8.JAMSAMPAI,t8.TOTALJAM,t7.NIK,DATE(t7.TJMASUK)AS TANGGAL,
		t8.JENISHARI,t7.TJMASUK AS JAMMASUK,MAX(t7.TJKELUAR)AS JAMKELUAR,
		SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR)) AS JAMKERJA, 
		IF(t8.JENISHARI ='N',(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0)),(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0))) as POTONGISTIRAHAT,
		(SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR))-IF(t8.JENISHARI ='N',(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0)),(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0)))) AS JAMBERSIH,
		IF(((SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR))-IF(t8.JENISHARI ='N',(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0)),(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0))))<t8.TOTALJAM),(t8.TOTALJAM-(SUM(TIMESTAMPDIFF(MINUTE,t7.TJMASUK,t7.TJKELUAR))-IF(t8.JENISHARI ='N',(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0)),(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT1S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT1S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT2S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT2S)),0))+(IF(TIME(t7.TJKELUAR)>t8.JAMREHAT3S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3M),TIMESTAMP(DATE(t7.TJMASUK),t8.JAMREHAT3S)),0))))),0) AS JAMKURANG,
		IF(t7.TJMASUK > TIMESTAMP(DATE(t7.TJMASUK),t8.JAMDARI),TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMDARI),t7.TJMASUK),0) AS TERLAMBAT,
		IF(t7.TJKELUAR < TIMESTAMP(DATE(t7.TJMASUK),t8.JAMSAMPAI),TIMESTAMPDIFF(MINUTE,t7.TJKELUAR,TIMESTAMP(DATE(t7.TJMASUK),t8.JAMSAMPAI)),0) AS PLGLBHAWAL
		FROM presensi t7 
		JOIN (
			SELECT t5.VALIDTO, t6.KODESHIFT, t6.NIK, t5.NAMASHIFT, t5.SHIFTKE,t5.JENISHARI, t5.JAMDARI, t5.JAMSAMPAI, 
			((HOUR(TIMEDIFF(t5.JAMDARI,t5.JAMSAMPAI))*60) + MINUTE(TIMEDIFF(t5.JAMDARI,t5.JAMSAMPAI)) - (IF(t5.JENISHARI ='J',90,60))) as TOTALJAM, 
			t5.TGLMULAI, t5.TGLSAMPAI, t5.JAMREHAT1M,t5.JAMREHAT1S, t5.JAMREHAT2M,t5.JAMREHAT2S, t5.JAMREHAT3M,t5.JAMREHAT3S
			FROM karyawanshift t6
			JOIN (
				SELECT t4.VALIDTO, t4.KODESHIFT, t1.NAMASHIFT, t1.SHIFTKE, t1.JENISHARI, t1.JAMDARI, t1.JAMSAMPAI, t4.TGLMULAI,t4.TGLSAMPAI,t1.JAMREHAT1M,t1.JAMREHAT1S,
				t1.JAMREHAT2M,t1.JAMREHAT2S,t1.JAMREHAT3M,t1.JAMREHAT3S
				FROM shiftjamkerja t1
				JOIN (
				SELECT t3.KODESHIFT, t2.NAMASHIFT, t3.SHIFTKE, t2.VALIDTO, t3.TGLMULAI, t3.TGLSAMPAI
				FROM shift t2
				RIGHT JOIN pembagianshift t3
				ON t2.NAMASHIFT=t3.NAMASHIFT) as t4
				ON t1.NAMASHIFT=t4.namashift AND t1.SHIFTKE=t4.SHIFTKE) as t5
			ON t5.KODESHIFT = t6.KODESHIFT ) as t8
		ON t7.NIK=t8.NIK AND DATE(t7.TJMASUK) <= DATE(t8.TGLSAMPAI) AND DATE(t7.TJMASUK) >= DATE(t8.TGLMULAI) AND (t8.JENISHARI = (IF(DAYNAME(DATE(t7.TJMASUK)) = 'Friday','J','N')))
		GROUP BY t7.NIK,DATE(t7.TJMASUK), t8.JENISHARI
		
		* -----------------------------------------
			
		
		
		*/
		
		$sql3 = "UPDATE hitungpresensi hp
		JOIN (
			SELECT t1.VALIDFROM, t1.VALIDTO, p.NAMASHIFT, p.NIK, p.SHIFTKE, p.TANGGAL,
				t1.POLASHIFT, SUBSTR(T1.POLASHIFT,DAYOFWEEK(p.TANGGAL),1) AS POLA,
				t1.JAMDARI, t1.JAMSAMPAI, t1.JENISHARI, p.TJMASUK, p.TJKELUAR,
				(TIMESTAMPDIFF(MINUTE, p.TJMASUK, p.TJKELUAR)) AS TOTALJAM,
				((TIMESTAMPDIFF(MINUTE, p.TJMASUK, p.TJKELUAR))-
			IF(t1.JENISHARI ='N',
			(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))+
(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))+
(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)),
(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))+
(IF(TIME(p.TJKELUAR)>t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))+
(IF(TIME(p.TJKELUAR)>t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)))) AS JAMBERSIH,
			IF(p.TJMASUK > TIMESTAMP(DATE(p.TJMASUK),t1.JAMDARI),TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMDARI),p.TJMASUK),0) AS TERLAMBAT,
			IF(p.TJKELUAR < TIMESTAMP(DATE(p.TJMASUK),t1.JAMSAMPAI),TIMESTAMPDIFF(MINUTE,p.TJKELUAR,TIMESTAMP(DATE(p.TJMASUK),t1.JAMSAMPAI)),0) AS PLGLBHAWAL,
			(IF(p.TJMASUK > TIMESTAMP(DATE(p.TJMASUK),t1.JAMDARI),TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMDARI),p.TJMASUK),0)+IF(p.TJKELUAR < TIMESTAMP(DATE(p.TJMASUK),t1.JAMSAMPAI),TIMESTAMPDIFF(MINUTE,p.TJKELUAR,TIMESTAMP(DATE(p.TJMASUK),t1.JAMSAMPAI)),0)) AS JAMKURANG,
			IF(t1.JENISHARI ='N',
			(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))+
(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))+
(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)),
(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))+
(IF(TIME(p.TJKELUAR)>t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))+
(IF(TIME(p.TJKELUAR)>t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0))) as POTONGISTIRAHAT
			FROM presensi p 
			JOIN (
				SELECT
					s.VALIDFROM,s.VALIDTO,sj.NAMASHIFT,sj.SHIFTKE,ds.POLASHIFT,sj.JENISHARI,sj.JAMDARI,sj.JAMSAMPAI,sj.JAMREHAT0M,sj.JAMREHAT0S,
					sj.JAMREHAT1M,sj.JAMREHAT1S,sj.JAMREHAT2M,sj.JAMREHAT2S,sj.JAMREHAT3M,sj.JAMREHAT3S,sj.JAMREHAT4M,sj.JAMREHAT4S
				FROM
					shift s
				RIGHT JOIN shiftjamkerja sj ON sj.NAMASHIFT=s.NAMASHIFT
				INNER JOIN detilshift ds ON ds.NAMASHIFT=s.NAMASHIFT AND ds.SHIFTKE=sj.SHIFTKE
			) AS t1 ON t1.NAMASHIFT=p.NAMASHIFT AND t1.SHIFTKE=p.SHIFTKE AND t1.JENISHARI=IF(DAYNAME(p.TANGGAL) = 'Friday','J','N')
		) AS t2
		ON t2.NIK=hp.NIK AND t2.TANGGAL=hp.TANGGAL AND (DATE(t2.TANGGAL)>=DATE('$tglmulai') AND DATE(t2.TANGGAL)<=DATE('$tglsampai'))
		SET
			hp.JENISABSEN=IF((IF(ABS((t2.JAMBERSIH)/60) > 0,1,0)) = 1,IF(t2.POLA = '0','OF','HD'),'AL'),
			hp.HARIKERJA=IF(ABS((t2.JAMBERSIH)/60) > 0,1,0),
			hp.JAMKERJA=ABS((t2.JAMBERSIH)/60),
			hp.XPOTONG=IF(hp.JENISABSEN = 'AL',1,0),
			hp.JAMKURANG=t2.TERLAMBAT+t2.PLGLBHAWAL,
			hp.TERLAMBAT=t2.TERLAMBAT,
			hp.PLGLBHAWAL=t2.PLGLBHAWAL;";
		$query3 = $this->db->query($sql3);
		
			
		// ------------------------------------------ 2013-09-19 Proses Update JamLembur Awal dan Akhir -------------------------------------------
		
		$sql4 = "UPDATE hitungpresensi hp
		JOIN (
		SELECT tl.NAMASHIFT,tl.NIK,tl.SHIFTKE,tl.TANGGAL,tl.POLASHIFT,tl.POLA,tl.JAMDARI,tl.JAMSAMPAI,tl.TOTALJAM,tl.JENISHARI,L.TJMASUK AS JAMMSKLEMBUR,tl.TJMASUK,tl.TJKELUAR,
		tl.JAMBERSIH,tl.POTONGISTIRAHAT,TIMESTAMPDIFF(MINUTE,L.TJMASUK,tl.TJKELUAR) AS JL, L.JENISLEMBUR,
		IF(L.TJMASUK >= tl.TJMASUK,TIMESTAMPDIFF(MINUTE,L.TJMASUK,tl.TJKELUAR)-
		(IF(tl.TJKELUAR > tl.JAMREHAT4S,
			TIMESTAMPDIFF(MINUTE,TIMESTAMP(tl.TANGGAL,tl.JAMREHAT4M),TIMESTAMP(tl.TANGGAL,tl.JAMREHAT4S)),0)),
			TIMESTAMPDIFF(MINUTE,L.TJMASUK,tl.TJMASUK)-
			(IF(tl.TJMASUK > tl.JAMREHAT0S,TIMESTAMPDIFF(MINUTE,TIMESTAMP(tl.TANGGAL,tl.JAMREHAT0M),TIMESTAMP(tl.TANGGAL,tl.JAMREHAT0S)),0))) AS JAMLEMBUR
		FROM presensilembur L
		JOIN (
			SELECT t1.VALIDFROM,t1.VALIDTO,p.NAMASHIFT,p.NIK,p.SHIFTKE,p.TANGGAL,t1.POLASHIFT,SUBSTR(T1.POLASHIFT,DAYOFWEEK(p.TANGGAL),1) AS POLA,t1.JAMDARI,t1.JAMSAMPAI,t1.JENISHARI,p.TJMASUK,p.TJKELUAR,
			(TIMESTAMPDIFF(MINUTE,p.TJMASUK,p.TJKELUAR)) AS TOTALJAM,
			((TIMESTAMPDIFF(MINUTE,p.TJMASUK,p.TJKELUAR))-
			IF(t1.JENISHARI ='N',
				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))	+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))				+(IF(TIME(p.TJKELUAR)>t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)),				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))
				+(IF(TIME(p.TJKELUAR)>t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0))
				)) AS JAMBERSIH,
			IF(t1.JENISHARI ='N',
				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)),				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))
				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0))
				) as POTONGISTIRAHAT,
			t1.JAMREHAT0M,t1.JAMREHAT0S,t1.JAMREHAT1M,t1.JAMREHAT1S,t1.JAMREHAT2M,t1.JAMREHAT2S,t1.JAMREHAT3M,t1.JAMREHAT3S,t1.JAMREHAT4M,t1.JAMREHAT4S
			FROM presensi p 
			JOIN (
				SELECT
					s.VALIDFROM,s.VALIDTO,sj.NAMASHIFT,sj.SHIFTKE,ds.POLASHIFT,sj.JENISHARI,sj.JAMDARI,sj.JAMSAMPAI,sj.JAMREHAT0M,sj.JAMREHAT0S,
					sj.JAMREHAT1M,sj.JAMREHAT1S,sj.JAMREHAT2M,sj.JAMREHAT2S,sj.JAMREHAT3M,sj.JAMREHAT3S,sj.JAMREHAT4M,sj.JAMREHAT4S
				FROM
					shift s
				RIGHT JOIN shiftjamkerja sj ON sj.NAMASHIFT=s.NAMASHIFT
				INNER JOIN detilshift ds ON ds.NAMASHIFT=s.NAMASHIFT AND ds.SHIFTKE=sj.SHIFTKE
			) AS t1 ON t1.NAMASHIFT=p.NAMASHIFT AND t1.SHIFTKE=p.SHIFTKE AND t1.JENISHARI=IF(DAYNAME(p.TANGGAL) = 'Friday','J','N')
			WHERE (p.TANGGAL >= t1.VALIDFROM AND p.TANGGAL <= t1.VALIDTO)
		) tl ON tl.NIK=L.NIK AND tl.TANGGAL=DATE(L.TJMASUK)
		) LB ON hp.NIK=LB.NIK AND hp.TANGGAL=LB.TANGGAL  AND (DATE(hp.TANGGAL)>=DATE('$tglmulai') AND DATE(hp.TANGGAL)<=DATE('$tglsampai'))
		SET
			hp.JENISABSEN=IF((IF(ABS((LB.JAMBERSIH)/60) > 0,1,0)) = 1,IF(LB.POLA = '0',IF(LB.JAMLEMBUR > 0, 'LB','OF'),'HD'),'AL'),
			hp.JAMKERJA=IF(LB.JENISLEMBUR != 'B', 0, ((LB.JAMBERSIH-LB.JL) /60)),
			hp.JAMLEMBUR=
				(IF(MOD(LB.JAMLEMBUR,60)<= 44 AND MOD(LB.JAMLEMBUR,60)>= 15,
					(LB.JAMLEMBUR - MOD(LB.JAMLEMBUR,60))+30,
				IF(MOD(LB.JAMLEMBUR,60) >= 45,
					(LB.JAMLEMBUR - MOD(LB.JAMLEMBUR,60))+60,
					LB.JAMLEMBUR - MOD(LB.JAMLEMBUR,60)))/60),
			hp.JENISLEMBUR=LB.JENISLEMBUR,
			hp.EXTRADAY=IF(LB.JENISLEMBUR != 'B', 1, 0);";
		$query4 = $this->db->query($sql4);
		
			
		// ------------------------------------------ 2013-11-28 Proses Update JamLembur Pada Hari Libur ------------------------------------
		
		$sql4 = "UPDATE hitungpresensi hp
		JOIN (
		SELECT tl.NAMASHIFT,tl.NIK,tl.SHIFTKE,tl.TANGGAL,tl.POLASHIFT,tl.POLA,tl.JAMDARI,tl.JAMSAMPAI,tl.TOTALJAM,tl.JENISHARI,L.TJMASUK AS JAMMSKLEMBUR,tl.TJMASUK,tl.TJKELUAR,
		tl.JAMBERSIH,tl.POTONGISTIRAHAT,TIMESTAMPDIFF(MINUTE,L.TJMASUK,tl.TJKELUAR) AS JL, L.JENISLEMBUR,
		IF(L.TJMASUK >= tl.TJMASUK,TIMESTAMPDIFF(MINUTE,L.TJMASUK,tl.TJKELUAR)-
		(IF(TIME(tl.TJKELUAR) >= tl.JAMREHAT4S AND TIME(tl.TJMASUK)<=tl.JAMREHAT4M,
			TIMESTAMPDIFF(MINUTE,TIMESTAMP(tl.TANGGAL,tl.JAMREHAT4M),TIMESTAMP(tl.TANGGAL,tl.JAMREHAT4S)),0)),
			TIMESTAMPDIFF(MINUTE,L.TJMASUK,tl.TJMASUK)-
			(IF(TIME(tl.TJKELUAR) >= tl.JAMREHAT0S AND TIME(tl.TJMASUK)<=tl.JAMREHAT0M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(tl.TANGGAL,tl.JAMREHAT0M),TIMESTAMP(tl.TANGGAL,tl.JAMREHAT0S)),0))) AS JAMLEMBUR
		FROM presensilembur L
		JOIN (
			SELECT t1.VALIDFROM,t1.VALIDTO,p.NAMASHIFT,p.NIK,p.SHIFTKE,p.TANGGAL,t1.POLASHIFT,SUBSTR(T1.POLASHIFT,DAYOFWEEK(p.TANGGAL),1) AS POLA,t1.JAMDARI,t1.JAMSAMPAI,t1.JENISHARI,p.TJMASUK,p.TJKELUAR,
			(TIMESTAMPDIFF(MINUTE,p.TJMASUK,p.TJKELUAR)) AS TOTALJAM,
			((TIMESTAMPDIFF(MINUTE,p.TJMASUK,p.TJKELUAR))-
			IF(t1.JENISHARI ='N',
				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)),				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))
				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0))
				)) AS JAMBERSIH,
			IF(t1.JENISHARI ='N',
				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0)),				(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT1S AND TIME(p.TJMASUK)<=t1.JAMREHAT1M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT1S)),0))				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT2S AND TIME(p.TJMASUK)<=t1.JAMREHAT2M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT2S)),0))
				+(IF(TIME(p.TJKELUAR)>=t1.JAMREHAT3S AND TIME(p.TJMASUK)<=t1.JAMREHAT3M,TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3M),TIMESTAMP(DATE(p.TJMASUK),t1.JAMREHAT3S)),0))
				) as POTONGISTIRAHAT,
			t1.JAMREHAT0M,t1.JAMREHAT0S,t1.JAMREHAT1M,t1.JAMREHAT1S,t1.JAMREHAT2M,t1.JAMREHAT2S,t1.JAMREHAT3M,t1.JAMREHAT3S,t1.JAMREHAT4M,t1.JAMREHAT4S
			FROM presensi p 
			JOIN (
				SELECT
					s.VALIDFROM,s.VALIDTO,sj.NAMASHIFT,sj.SHIFTKE,ds.POLASHIFT,sj.JENISHARI,sj.JAMDARI,sj.JAMSAMPAI,sj.JAMREHAT0M,sj.JAMREHAT0S,
					sj.JAMREHAT1M,sj.JAMREHAT1S,sj.JAMREHAT2M,sj.JAMREHAT2S,sj.JAMREHAT3M,sj.JAMREHAT3S,sj.JAMREHAT4M,sj.JAMREHAT4S
				FROM
					shift s
				RIGHT JOIN shiftjamkerja sj ON sj.NAMASHIFT=s.NAMASHIFT
				INNER JOIN detilshift ds ON ds.NAMASHIFT=s.NAMASHIFT AND ds.SHIFTKE=sj.SHIFTKE
			) AS t1 ON t1.NAMASHIFT=p.NAMASHIFT AND t1.SHIFTKE=p.SHIFTKE AND t1.JENISHARI=IF(DAYNAME(p.TANGGAL) = 'Friday','J','N')
			WHERE (p.TANGGAL >= t1.VALIDFROM AND p.TANGGAL <= t1.VALIDTO)
		) tl ON tl.NIK=L.NIK AND tl.TANGGAL=DATE(L.TJMASUK)
		) LB ON hp.NIK=LB.NIK AND hp.TANGGAL=LB.TANGGAL  AND (DATE(hp.TANGGAL)>=DATE('$tglmulai') AND DATE(hp.TANGGAL)<=DATE('$tglsampai'))
		SET
			hp.TERLAMBAT=IF(hp.JENISABSEN='LB' AND hp.JAMLEMBUR > 0,NULL,hp.TERLAMBAT),
			hp.PLGLBHAWAL=IF(hp.JENISABSEN='LB' AND hp.JAMLEMBUR > 0,NULL,hp.PLGLBHAWAL),
			hp.JAMKURANG=IF(hp.JENISABSEN='LB' AND hp.JAMLEMBUR > 0,NULL,hp.JAMKURANG),
			hp.JENISABSEN=IF((IF(ABS((LB.JAMBERSIH)/60) > 0,1,0)) = 1,IF(LB.POLA = '0',IF(LB.JAMLEMBUR > 0, 'LB','OF'),'HD'),'AL'),
			hp.JAMKERJA=IF(LB.JENISLEMBUR != 'B', 0, ((LB.JAMBERSIH-LB.JL) /60)),
			hp.JAMLEMBUR=IF(hp.JENISABSEN='LB' AND hp.JAMLEMBUR > 0,(IF(MOD((LB.JAMLEMBUR-LB.POTONGISTIRAHAT),60)<= 44 AND MOD((LB.JAMLEMBUR-LB.POTONGISTIRAHAT),60)>= 15,
					((LB.JAMLEMBUR-LB.POTONGISTIRAHAT) - MOD((LB.JAMLEMBUR-LB.POTONGISTIRAHAT),60))+30,
				IF(MOD((LB.JAMLEMBUR-LB.POTONGISTIRAHAT),60) >= 45,
					((LB.JAMLEMBUR-LB.POTONGISTIRAHAT) - MOD((LB.JAMLEMBUR-LB.POTONGISTIRAHAT),60))+60,
					(LB.JAMLEMBUR-LB.POTONGISTIRAHAT) - MOD((LB.JAMLEMBUR-LB.POTONGISTIRAHAT),60)))/60),hp.JAMLEMBUR);";
		$query4 = $this->db->query($sql4);
		
		
		// ------------------------------------------ Proses 5 Update Permohonan Ijin pada Jenis Absen
		$sql5 = "UPDATE hitungpresensi T2
		JOIN (
			SELECT T1.TANGGAL,T1.NIK,T1.JENISABSEN,ja.KELABSEN,ja.POTONG,T1.AMBILCUTI,T1.STATUSIJIN, T1.JAMDARI,t1.JAMSAMPAI,
			TIMESTAMPDIFF(MINUTE,TIMESTAMP(DATE(T1.TANGGAL),T1.JAMDARI),TIMESTAMP(DATE(T1.TANGGAL),T1.JAMSAMPAI)) AS TOTALIJIN
			FROM permohonanijin T1
			INNER JOIN jenisabsen ja ON ja.JENISABSEN=T1.JENISABSEN
			WHERE T1.STATUSIJIN='T' AND T1.TANGGAL >= DATE('$tglmulai') AND T1.TANGGAL <= DATE('$tglsampai')) AS T3
		ON T2.NIK=T3.NIK AND T2.TANGGAL=T3.TANGGAL
		SET	
			T2.JAMKERJA=IF(T3.KELABSEN='P',((T2.JAMKERJA*60)-T3.TOTALIJIN)/60,T2.JAMKERJA),
			T2.JAMKURANG=IF(T3.KELABSEN='P',((T2.JAMKURANG)+T3.TOTALIJIN),T2.JAMKURANG),
			T2.JENISABSEN=IF(T3.KELABSEN='P','HD',T3.JENISABSEN),
			T2.XPOTONG=IF(T3.POTONG='C' AND T3.KELABSEN='I' AND T3.AMBILCUTI='0', 1, 0),
			T2.IZINPRIBADI=T3.TOTALIJIN";
			
		$query5 = $this->db->query($sql5);
		
		// ------------------------------------------ Proses 6 Update Permohonan Cuti pada Jenis Absen
		$sql6 = "UPDATE hitungpresensi T4
		JOIN (
		SELECT t1.NOURUT,t1.NIK,t1.JENISABSEN,t1.LAMA,t1.TGLMULAI,t1.TGLSAMPAI,t1.SISACUTI,t1.STATUSCUTI
		FROM rinciancuti t1
		RIGHT JOIN permohonancuti t2
		ON t1.NOCUTI=t2.NOCUTI
		WHERE t1.STATUSCUTI='T' AND t1.TGLMULAI >= DATE('$tglmulai') AND t1.TGLSAMPAI <= DATE('$tglsampai')) AS T3
		ON T4.NIK=T3.NIK AND (T4.TANGGAL=T3.TGLMULAI OR t4.TANGGAL=T3.TGLSAMPAI)
		SET
			T4.JENISABSEN=T3.JENISABSEN";
		$query6 = $this->db->query($sql6);
		
		// ------------------------------------------ Proses 7 Update Untuk Pembulatan JAM KURANG
		$sql7 = "UPDATE hitungpresensi
		SET
			JAMKURANG = (IF(MOD(JAMKURANG,60)<= 30 AND MOD(JAMKURANG,60)>=1,(JAMKURANG - MOD(JAMKURANG,60))+30,IF(MOD(JAMKURANG,60)>30,(JAMKURANG - MOD(JAMKURANG,60))+60,JAMKURANG))/60)
		WHERE TANGGAL >= DATE('$tglmulai') AND TANGGAL <= DATE('$tglsampai') AND JAMKURANG > 0";
		$query7 = $this->db->query($sql7);
		
		// ------------------------------------------ Proses 9 Update Untuk JENIS ABSEN YANG ADA PRESENSI
		$sql9 = "UPDATE hitungpresensi hpu
		JOIN (
		SELECT hp.NIK,k.AGAMA AS AGAMAKAR,hp.TANGGAL,
		(IF(kl.JENISLIBUR = 'P' OR kl.JENISLIBUR = 'N' OR kl.JENISLIBUR = 'A','LB','AL')) AS JENISABSEN,
		kl.JENISLIBUR,kl.AGAMA
		FROM hitungpresensi hp
		INNER JOIN karyawan k ON k.NIK=hp.NIK
		INNER JOIN kalenderlibur kl ON kl.TANGGAL=hp.TANGGAL
		WHERE JENISABSEN='AL' AND hp.TANGGAL >= DATE('$tglmulai') AND hp.TANGGAL <= DATE('$tglsampai')
		) AS t1 ON t1.NIK=hpu.NIK AND t1.TANGGAL=hpu.TANGGAL
		SET
			hpu.JENISABSEN = t1.JENISABSEN";
		$query9 = $this->db->query($sql9);
		
		// ------------------------------------------ Proses 10 Update Untuk JENISABSEN TIDAK ADA PRESENSI
		$sql10 = "UPDATE hitungpresensi hpu
		JOIN (
			SELECT hp.TANGGAL,hp.NIK,hp.JENISABSEN,
			(IF(SUBSTR(tp.POLASHIFT,DAYOFWEEK(hp.TANGGAL),1) = '0','OF','AL')) AS JENISABSENPOLA
			FROM hitungpresensi hp
			JOIN (
				SELECT p.NIK,ds.POLASHIFT
				FROM presensi p
				INNER JOIN detilshift ds ON ds.NAMASHIFT=p.NAMASHIFT AND ds.SHIFTKE=p.SHIFTKE
			) tp ON tp.NIK=hp.NIK
			WHERE hp.JENISABSEN = 'AL' AND hp.TANGGAL >= DATE('$tglmulai') AND hp.TANGGAL <= DATE('$tglsampai')
		) t1 ON t1.NIK=hpu.NIK AND t1.TANGGAL=hpu.TANGGAL
		SET
			hpu.JENISABSEN=t1.JENISABSENPOLA,
			hpu.XPOTONG=IF(t1.JENISABSENPOLA='AL',1,0)";
		$query10 = $this->db->query($sql10);
		
		
		// ------------------------------------------ Proses 8 Update Untuk SATUAN LEMBUR
		$sql8 = "UPDATE hitungpresensi hp
		JOIN (
			SELECT h.TANGGAL,h.BULAN,h.NIK,h.JENISLEMBUR,h.JAMLEMBUR,
				l.BATAS1,l.BATAS2,l.BATAS3,l.PENGALI1,l.PENGALI2,l.PENGALI3,
				IF (h.JAMLEMBUR <= l.BATAS1, h.JAMLEMBUR*l.PENGALI1,
					IF (l.BATAS2 <= 0,(l.BATAS1*l.PENGALI1)+(h.JAMLEMBUR-l.BATAS1)*l.PENGALI2,
						(l.BATAS1*l.PENGALI1)+(l.BATAS2*l.PENGALI2)+(h.JAMLEMBUR-l.BATAS1-l.BATAS2)*l.PENGALI3)
				) AS SATLEMBUR
			FROM (
				SELECT *
				FROM hitungpresensi
				WHERE TANGGAL >= DATE('$tglmulai') AND TANGGAL <= DATE('$tglsampai')
				) as h
			INNER JOIN lembur l ON l.JENISLEMBUR=h.JENISLEMBUR
			WHERE (h.JENISLEMBUR IS NOT NULL) AND (h.TANGGAL >= l.VALIDFROM) AND (h.BULAN >= l.BULANMULAI AND h.BULAN <= l.BULANSAMPAI)
		) AS t1 ON t1.TANGGAL=hp.TANGGAL AND t1.NIK=hp.NIK
		SET
			hp.SATLEMBUR = t1.SATLEMBUR;";
		$query8 = $this->db->query($sql8);
		
		
	}
	
	function InitRecord($bulangaji){
		$cnt = 0;
		$this->db->where(array('PARAMETER' => 'Total Data Hit'))->update('init', array('VALUE'=>$cnt));
		$this->db->where(array('PARAMETER' => 'CounterHit'))->update('init', array('VALUE'=>$cnt));
		
		$bln = $bulangaji . "01";
		$this->db->select('TGLMULAI,TGLSAMPAI');
		$sql = $this->db->get_where('periodegaji',array('BULAN' => $bulangaji))->result_array();
		
		/*$TMASUK = new DateTime($sql[0]['TGLMULAI']);
		$TSAMPAI = new DateTime($sql[0]['TGLSAMPAI']);
		$TM = intval($TMASUK->format('d'));
		$TS = intval($TSAMPAI->format('d'));*/
		
		$this->db->truncate('datetemp');
		
		$tglAwal = $sql[0]['TGLMULAI'];
		
		$tglAkhir = $sql[0]['TGLSAMPAI'];
		$date1 = date_create($tglAwal);
		$arr1 = explode('-',$tglAwal);
		$arr2 = explode('-',$tglAkhir);
		$diff = gregoriantojd($arr2[1], $arr2[2], $arr2[0])- gregoriantojd($arr1[1], $arr1[2], $arr1[0]);
		$this->db->where(array('PARAMETER' => 'Total Data Hit'))->update('init', array('VALUE'=>$diff));
		for($k=0;$k<=$diff;$k++){                
			//echo date('Y-m-d', strtotime(date_format($date1,"Y-m-d")));
			//$this->firephp->log(date('Y-m-d', strtotime(date_format($date1,"Y-m-d"))));
			$this->db->insert('datetemp', array('tanggal'=>date('Y-m-d', strtotime(date_format($date1,'Y-m-d')))));
			date_add($date1, date_interval_create_from_date_string('1 days'));
			$cnt ++;
			$this->db->where(array('PARAMETER' => 'CounterHit'))->update('init', array('VALUE'=>$cnt));
		}
		
		$sql_init = "INSERT INTO hitungpresensi (NIK, BULAN, TANGGAL, JENISABSEN, USERNAME)
			SELECT v_presensi.NIK, '".$bulangaji."', v_datetemp.tanggal_init, 'AL',
				'".$this->session->userdata('user_name')."'
			FROM (
					SELECT *
					FROM presensi
					WHERE TO_DAYS(TJMASUK)
						BETWEEN TO_DAYS('".$tglAwal."') AND TO_DAYS('".$tglAkhir."')
					GROUP BY NIK
				) AS v_presensi,
				(
					SELECT tanggal AS tanggal_init
					FROM datetemp
					WHERE TO_DAYS(tanggal)
						BETWEEN TO_DAYS('".$tglAwal."') AND TO_DAYS('".$tglAkhir."')
				) AS v_datetemp";
		$this->db->query($sql_init);
		/*for($i=$TM;$i<=$TS;$i++)
		{
			$sql = "insert into HITUNGPRESENSI 
					(NIK, BULAN, TANGGAL, JENISABSEN, USERNAME) 
					select NIK, $bulangaji as BULAN, '".$TMASUK->format('Y-m')."-".$i."' as TANGGAL, 'AL' as JENISABSEN,
					'".$this->session->userdata('user_name')."' as USERNAME 
					from PRESENSI 
					where DATE_FORMAT(TJMASUK,'%Y%m')=DATE_FORMAT(DATE_SUB('$bln',INTERVAL 1 MONTH),'%Y%m')
					GROUP BY NIK";
			$query = $this->db->query($sql);
			
		}*/
	}
	
	function LoopUpdate($bulangaji,$tglmulai,$tglsampai){		
		
		$sql = "SELECT BULAN FROM hitungpresensi WHERE BULAN = '$bulangaji' GROUP BY BULAN";
		$query = $this->db->query($sql)->result_array();
		
		/*if(sizeof($query) > 0)
		{
			//$this->ProsesUpdate($bulangaji);
			$this->ProcessUpdate($tglmulai,$tglsampai);			
		}
		else
		{
			$this->InitRecord($bulangaji);
			$this->ProcessUpdate($tglmulai,$tglsampai);			
		}*/
		
		if(sizeof($query) == 0){
			$this->InitRecord($bulangaji);
		}
		$this->ProcessUpdate($tglmulai,$tglsampai);
		
		$total  = $this->db->get('hitungpresensi')->num_rows();
		$last   = $this->db->select('NIK, BULAN,TANGGAL,JENISABSEN,HARIKERJA,JAMKERJA,JAMLEMBUR,JAMKURANG')->order_by('NIK', 'ASC')->get('hitungpresensi')->row();
		$json	= array(
			'success'   => TRUE,
			'message'   => 'Process Successfully.',
			'total'     => $total,
			'data'      => $last
		);
		
		return $json;
	}
	
	function get_periodegaji(){
		$sql = "SELECT periodegaji.BULAN,
				CONCAT(bulan.bulan_nama,', ',SUBSTRING(periodegaji.BULAN,1,4)) AS BULAN_GAJI,
				TGLMULAI, TGLSAMPAI
			FROM periodegaji JOIN bulan ON(bulan.bulan_kode = SUBSTRING(periodegaji.BULAN,-2))
			ORDER BY BULAN DESC";
		$query  = $this->db->query($sql)->result();
		$total  = $this->db->get('periodegaji')->num_rows();
		
		$data   = array();
		foreach($query as $result){
			$data[] = $result;
		}
		
		$json	= array(
						'success'   => TRUE,
						'message'   => "Loaded data",
						'total'     => $total,
						'data'      => $data
		);
		
		return $json;
	}
	
	function HitungPresensi($bulan,$nik, $tglmulai, $tglsampai){
		/*
		 * Langkah memproses Perhitungan Presensi untuk seluruh Karyawan		 
		 * 1. Persiapan Tabel Penunjang
		 * 1.a. Tabel PRESENSI			--> Berisi data Presensi seluruh karyawan
		 * 1.b. Tabel HITUNGPRESENSI	--> Akan Berisi data setelah proses perhitungan presensi
		 * 1.c. Tabel PERIODEGAJI		--> Berisi data penunjang dlm perhitungan bulan dlm presensi
		 * 1.d. Tabel JENISABSEN		--> Berisi data kode Jenis Absen
		 * 1.e. Tabel KARYAWANSHIFT		--> Berisi data Kode dan Nama Shift Karyawan -> berhub dgn tbel Shift
		 * 
		 * Perhitungan dilakukan Per Tanggal dari Tanggal Mulai- Tanggal SAmpai untuk setiap Karyawan...
		 *
		 * 2. Persiapan Data Perhitungan Lembur
		 * 		Lembur pada hari kerja normal
		 * 		Lembur pada hari libur sabtu minggu/nasional
		 * 		Lembur pada hari libur keagamaan
		 * Kondisi Lembur :
		 *		- Tambahkan Field JENISLEMBUR pada HITUNGPRESENSI
		 *		- Dalam list Lembur tambahkan lookup AGAMA dari NIK bersangkutan bila dia masuk pada hari liburnya agama lain.
		 *		- Tambahkan fungsi Update untuk JAMKERJA, JAMLEMBUR, EXTRADAY
		 *
		 *
		 * Kondisi Bolos :
		 *		- Persiapan data dari tabel PERMOHONANCUTI DAN RINCIANCUTI
		 *		- Persiapan data dari tabel PERMOHONANIJIN
		 *
		 *
		 * Kondisi Inisialisasi :
		 *		- Proses Inisialisasi harus Cek harinya, Apa hari Sabtu ato Minggu klo iya JenisAbsen LB -> Libur
		 *		- Persiapan data dari tabel PERMOHONANIJIN
		 *
		 *
		 *
		 *
		 *
		 * 2.a. cek db.karyawanshift => digunakan untuk mendapatkan KODESHIFT
		 * 2.b. cek db.pembagianshift => Untuk mendapatkan NAMASHIFT SHIFTKE TGLMULAI, TGLSAMPAI
		 * 2.c. cek db.shiftjamkerja => Untuk mendapatkan JAMDARI JAMSAMPAI
		 * * 
		 * 3. Persiapan Data perhitungan Lembur dan ExtraDay
		 * 3.a. cek db.splembur => apakah ada perintah surat lembur untuk menentukan jam lembur
		 * 3.b. cek db.rencanalembur => apakah ada perintah surat lembur untuk menentukan jam lembur
		 * 3.c. cek db.kalenderlibur => apakah karyawan bekerja pada hari libur atau tidak untuk menentukan ExtraDay...
		 * 
		 */
		 
		/* 2.a. */
		$kodeshift = $this->db->get_where('karyawanshift', array('NIK'=>$nik));
		if($kodeshift->num_rows() == 0){
			$kodeshift = $this->db->get_where('karyawanshift', array('NIK'=>$nik));
		}
		
	}
	
	function getAll($start, $page, $limit){
		$sql = "SELECT h.NIK, k.NAMAKAR as NAMA, h.BULAN, h.TANGGAL, h.JENISABSEN, h.HARIKERJA, h.JAMKERJA, h.JAMLEMBUR, h.JAMKURANG, h.JAMBOLOS,
		h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING
		FROM hitungpresensi h
		INNER JOIN karyawan k ON k.NIK=h.NIK
		ORDER BY h.TANGGAL ASC
		LIMIT $start,$limit";
		$query = $this->db->query($sql)->result();
		//$query  = $this->db->limit($limit, $start)->order_by('TANGGAL', 'ASC')->get('hitungpresensi')->result();
		$total  = $this->db->get('hitungpresensi')->num_rows();
		
		$data   = array();
		foreach($query as $result){
			$data[] = $result;
		}
		
		$json	= array(
			'success'   => TRUE,
			'message'   => "Loaded data",
			'total'     => $total,
			'data'      => $data
		);
		
		return $json;
	}
	
	/**
	 * Fungsi	: save
	 * 
	 * Untuk menambah data baru atau mengubah data lama
	 * 
	 * @param array $data
	 * @return json
	 */
	function save($data){
		$last   = NULL;
		
		$pkey = array('NIK'=>$data->NIK,'BULAN'=>$data->BULAN,'TANGGAL'=>date('Y-m-d', strtotime($data->TANGGAL)));
		
		if($this->db->get_where('hitungpresensi', $pkey)->num_rows() > 0){
			/*
			 * Data Exist
			 */
			
			$arrdatau = array('JENISABSEN'=>$data->JENISABSEN,'HARIKERJA'=>$data->HARIKERJA,'JAMKERJA'=>$data->JAMKERJA,'JAMLEMBUR'=>$data->JAMLEMBUR,'JAMKURANG'=>$data->JAMKURANG,'JAMBOLOS'=>$data->JAMBOLOS,'EXTRADAY'=>$data->EXTRADAY,'TERLAMBAT'=>$data->TERLAMBAT,'PLGLBHAWAL'=>$data->PLGLBHAWAL,'USERNAME'=>$data->USERNAME,'POSTING'=>$data->POSTING);
			 
			$this->db->where($pkey)->update('hitungpresensi', $arrdatau);
			$last   = $data;
			
		}else{
			/*
			 * Data Not Exist
			 * 
			 * Process Insert
			 */
			
			$arrdatac = array('NIK'=>$data->NIK,'BULAN'=>$data->BULAN,'TANGGAL'=>(strlen(trim($data->TANGGAL)) > 0 ? date('Y-m-d', strtotime($data->TANGGAL)) : NULL),'JENISABSEN'=>$data->JENISABSEN,'HARIKERJA'=>$data->HARIKERJA,'JAMKERJA'=>$data->JAMKERJA,'JAMLEMBUR'=>$data->JAMLEMBUR,'JAMKURANG'=>$data->JAMKURANG,'JAMBOLOS'=>$data->JAMBOLOS,'EXTRADAY'=>$data->EXTRADAY,'TERLAMBAT'=>$data->TERLAMBAT,'PLGLBHAWAL'=>$data->PLGLBHAWAL,'USERNAME'=>$data->USERNAME,'POSTING'=>$data->POSTING);
			 
			$this->db->insert('hitungpresensi', $arrdatac);
			$last   = $this->db->where($pkey)->get('hitungpresensi')->row();
			
		}
		
		$total  = $this->db->get('hitungpresensi')->num_rows();
		
		$json   = array(
						"success"   => TRUE,
						"message"   => 'Data berhasil disimpan',
						"total"     => $total,
						"data"      => $last
		);
		
		return $json;
	}
	
	/**
	 * Fungsi	: delete
	 * 
	 * Untuk menghapus satu data
	 * 
	 * @param array $data
	 * @return json
	 */
	function delete($data){
		$pkey = array('NIK'=>$data->NIK,'BULAN'=>$data->BULAN,'TANGGAL'=>date('Y-m-d', strtotime($data->TANGGAL)));
		
		$this->db->where($pkey)->delete('hitungpresensi');
		
		$total  = $this->db->get('hitungpresensi')->num_rows();
		$last = $this->db->get('hitungpresensi')->result();
		
		$json   = array(
						"success"   => TRUE,
						"message"   => 'Data berhasil dihapus',
						"total"     => $total,
						"data"      => $last
		);				
		return $json;
	}
	
	/*function getAll($tglmulai, $tglsampai,$saring,$sort,$filters,$start, $page, $limit){
		if($saring == "Range" && $filters == null)
		{
			$sql = "SELECT h.NIK, k.NAMAKAR as NAMA, h.BULAN, h.TANGGAL, h.JENISABSEN, h.HARIKERJA, h.JAMKERJA, h.JAMLEMBUR, h.JAMKURANG, h.JAMBOLOS,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK
			WHERE h.TANGGAL >= DATE('$tglmulai') AND h.TANGGAL <= DATE('$tglsampai')";
			$sql .= " ORDER BY k.NAMAKAR ASC";
			//$sql .= " LIMIT ".$start.",".$limit;		
			$query = $this->db->query($sql);
			
			//$this->db->where('TJKELUAR IS NULL', NULL);
			//$this->db->or_where('TJMASUK = TJKELUAR', NULL); 
			//$query  = $this->db->limit($limit, $start)->order_by('TJMASUK', 'ASC')->get('presensi');
			$total  = $query->num_rows();
			
			$data   = array();
			foreach($query->result() as $result){
				$data[] = $result;
			}
			
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total,
				'data'      => $data
			);
			
			return $json;
		}
		else
		{
			if (is_array($sorts)) {
				$encoded = false;
			} else {
				$encoded = true;
				$sorts = json_decode($sorts);
			}
			$dsort = ' h.NIK ASC';
			$ks = '';
			
			if (is_array($sorts)) {
				for ($i=0;$i<count($sorts);$i++){
					$sort = $sorts[$i];

					// assign Sort data (location depends if encoded or not)
					if ($encoded) {
						if($sort->property == 'NIK')
							$prop = "h.".$sort->property;
						elseif($sort->property == 'NAMAKAR')
							$prop = "k.".$sort->property;
						elseif($sort->property == 'NAMAUNIT')
							$prop = "uk.".$sort->property;
						elseif($sort->property == 'NAMAKEL')
							$prop = "kk.".$sort->property;
						else if($sort->property == 'TANGGAL')
							$prop = "h.".$sort->property;
						elseif($sort->property == 'TJMASUK')
							$prop = "h.".$sort->property;
						elseif($sort->property == 'TJKELUAR')
							$prop = "h.".$sort->property;
						else
							$prop = $sort->property;
						
						$dir = $sort->direction;					
					} else {
						$prop = $sort['property'];
						$dir = $sort['direction'];
					}
					$ks .= ",".$prop." ".$dir;
				}
				$dsort .= $ks;
			}
			//$this->firephp->info($dsort);

			// GridFilters sends filters as an Array if not json encoded
			if (is_array($filters)) {
				$encoded = false;
			} else {
				$encoded = true;
				$filters = json_decode($filters);
			}

			$where = ' 0 = 0 ';
			$qs = '';

			// loop through filters sent by client
			if (is_array($filters)) {
				for ($i=0;$i<count($filters);$i++){
					$filter = $filters[$i];

					// assign filter data (location depends if encoded or not)
					if ($encoded) {
						if($filter->field == 'NIK')
							$field = "h.".$filter->field;
						else
							$field = $filter->field;
							
						$value = $filter->value;
						$compare = isset($filter->comparison) ? $filter->comparison : null;
						$filterType = $filter->type;
					} else {
						$field = $filter['field'];
						$value = $filter['data']['value'];
						$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
						$filterType = $filter['data']['type'];
					}

					switch($filterType){
						case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
						case 'list' :
							if (strstr($value,',')){
								$fi = explode(',',$value);
								for ($q=0;$q<count($fi);$q++){
									$fi[$q] = "'".$fi[$q]."'";
								}
								$value = implode(',',$fi);
								$qs .= " AND ".$field." IN (".$value.")";
							}else{
								$qs .= " AND ".$field." = '".$value."'";
							}
						Break;
						case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
						case 'numeric' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
								case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
								case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
							}
						Break;
						case 'date' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
							}
						Break;
						case 'datetime' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
							}
						Break;
					}
				}
				$where .= $qs;
			}
			
			$sql = "SELECT h.NIK, k.NAMAKAR as NAMA, h.BULAN, h.TANGGAL, h.JENISABSEN, h.HARIKERJA, h.JAMKERJA, h.JAMLEMBUR, h.JAMKURANG, h.JAMBOLOS,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK
			WHERE ".$where;
			
			$sql .= " ORDER BY ".$dsort;
			$sql .= " LIMIT ".$start.",".$limit;		
			$query = $this->db->query($sql)->result();
			//$total = $query->num_rows();
			
			$total  = $this->db->query("SELECT count(h.NIK) AS total, k.NAMAKAR as NAMA, h.BULAN, h.TANGGAL, h.JENISABSEN, h.HARIKERJA, h.JAMKERJA, h.JAMLEMBUR, h.JAMKURANG, h.JAMBOLOS,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK WHERE ".$where)->result();
			$data   = array();
			foreach($query as $result){
				$data[] = $result;
			}
			//$this->firephp->info($sql);
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total[0]->total,
				//'total'     => $total,
				'data'      => $data
			);
			
			return $json;
		}
	}*/
	
	function getAllData($tglmulai, $tglsampai,$saring,$sorts,$filters,$start, $page, $limit)
	{	
		if($saring == "Range" && $filters == null)
		{
			$sql = "SELECT h.NIK, k.NAMAKAR, uk.NAMAUNIT, kk.NAMAKEL
			, h.BULAN, h.TANGGAL, h.JENISABSEN,
			h.HARIKERJA, h.JAMKERJA, h.JENISLEMBUR, h.JAMLEMBUR, h.SATLEMBUR, h.JAMKURANG,
			h.JAMBOLOS, h.IZINPRIBADI,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING, h.XPOTONG
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK
			INNER JOIN unitkerja uk ON uk.KODEUNIT=k.KODEUNIT
			INNER JOIN kelompok	kk ON kk.KODEKEL=k.KODEKEL
			WHERE h.TANGGAL >= DATE('$tglmulai') AND h.TANGGAL <= DATE('$tglsampai')";
			$sql .= " ORDER BY k.NAMAKAR ASC";
			$sql .= " LIMIT ".$start.",".$limit;		
			$query = $this->db->query($sql);
			
			//$this->db->where('TJKELUAR IS NULL', NULL);
			//$this->db->or_where('TJMASUK = TJKELUAR', NULL); 
			//$query  = $this->db->limit($limit, $start)->order_by('TJMASUK', 'ASC')->get('presensi');
			$total  = $this->db->query("SELECT count(h.NIK) as total, k.NAMAKAR, uk.NAMAUNIT, kk.NAMAKEL, h.BULAN, h.TANGGAL, h.JENISABSEN,
			h.HARIKERJA, h.JAMKERJA, h.JENISLEMBUR, h.JAMLEMBUR, h.SATLEMBUR, h.JAMKURANG,
			h.JAMBOLOS, h.IZINPRIBADI,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING, h.XPOTONG
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK
			INNER JOIN unitkerja uk ON uk.KODEUNIT=k.KODEUNIT
			INNER JOIN kelompok	kk ON kk.KODEKEL=k.KODEKEL
			WHERE h.TANGGAL >= DATE('$tglmulai') AND h.TANGGAL <= DATE('$tglsampai')")->result();
			
			$data   = array();
			foreach($query->result() as $result){
				$data[] = $result;
			}
			
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total[0]->total,
				'data'      => $data
			);
			
			return $json;
		}
		else
		{
			if (is_array($sorts)) {
					$encoded = false;
				} else {
					$encoded = true;
					$sorts = json_decode($sorts);
				}
				$dsort = ' h.NIK ASC';
				$ks = '';
				
				if (is_array($sorts)) {
					for ($i=0;$i<count($sorts);$i++){
						$sort = $sorts[$i];

						// assign Sort data (location depends if encoded or not)
						if ($encoded) {
							if($sort->property == 'NIK')
								$prop = "h.".$sort->property;
							elseif($sort->property == 'NAMAKAR')
								$prop = "k.".$sort->property;
							elseif($sort->property == 'NAMAUNIT')
								$prop = "uk.".$sort->property;
							elseif($sort->property == 'NAMAKEL')
								$prop = "kk.".$sort->property;
							else if($sort->property == 'TANGGAL')
								$prop = "h.".$sort->property;
							elseif($sort->property == 'TJMASUK')
								$prop = "h.".$sort->property;
							elseif($sort->property == 'TJKELUAR')
								$prop = "h.".$sort->property;
							else
								$prop = $sort->property;
							
							$dir = $sort->direction;					
						} else {
							$prop = $sort['property'];
							$dir = $sort['direction'];
						}
						$ks .= ",".$prop." ".$dir;
					}
					$dsort .= $ks;
				}
			// GridFilters sends filters as an Array if not json encoded
			if (is_array($filters)) {
				$encoded = false;
			} else {
				$encoded = true;
				$filters = json_decode($filters);
			}

			$where = ' 0 = 0 ';
			$qs = '';

			// loop through filters sent by client
			if (is_array($filters)) {
				for ($i=0;$i<count($filters);$i++){
					$filter = $filters[$i];

					// assign filter data (location depends if encoded or not)
					if ($encoded) {
						if($filter->field == 'NIK')
							$field = "h.".$filter->field;
						else
							$field = $filter->field;
							
						$value = $filter->value;
						$compare = isset($filter->comparison) ? $filter->comparison : null;
						$filterType = $filter->type;
					} else {
						$field = $filter['field'];
						$value = $filter['data']['value'];
						$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
						$filterType = $filter['data']['type'];
					}

					switch($filterType){
						case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
						case 'list' :
							if (strstr($value,',')){
								$fi = explode(',',$value);
								for ($q=0;$q<count($fi);$q++){
									$fi[$q] = "'".$fi[$q]."'";
								}
								$value = implode(',',$fi);
								$qs .= " AND ".$field." IN (".$value.")";
							}else{
								$qs .= " AND ".$field." = '".$value."'";
							}
						Break;
						case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
						case 'numeric' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
								case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
								case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
							}
						Break;
						case 'date' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
							}
						Break;
						case 'datetime' :
							switch ($compare) {
								case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
								case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d H:i:s',strtotime($value))."'"; Break;
							}
						Break;
					}
				}
				$where .= $qs;
			}
			
			$sql = "SELECT h.NIK, k.NAMAKAR, uk.NAMAUNIT, kk.NAMAKEL, h.BULAN, h.TANGGAL, h.JENISABSEN,
			h.HARIKERJA, h.JAMKERJA, h.JENISLEMBUR, h.JAMLEMBUR, h.SATLEMBUR, h.JAMKURANG,
			h.JAMBOLOS, h.IZINPRIBADI,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING, h.XPOTONG
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK
			INNER JOIN unitkerja uk ON uk.KODEUNIT=k.KODEUNIT
			INNER JOIN kelompok	kk ON kk.KODEKEL=k.KODEKEL
			WHERE ".$where;
			
			//$sql .= " ORDER BY k.NAMAKAR ASC";
			$sql .= " ORDER BY ".$dsort;
			$sql .= " LIMIT ".$start.",".$limit;		
			$query = $this->db->query($sql)->result();
			//$total = $query->num_rows();
			
			$total  = $this->db->query("SELECT count(h.NIK) as total, k.NAMAKAR, uk.NAMAUNIT, k.KODEKEL, h.BULAN, h.TANGGAL, h.JENISABSEN,
			h.HARIKERJA, h.JAMKERJA, h.JENISLEMBUR, h.JAMLEMBUR, h.SATLEMBUR, h.JAMKURANG,
			h.JAMBOLOS, h.IZINPRIBADI,
			h.EXTRADAY, h.TERLAMBAT, h.PLGLBHAWAL, h.USERNAME, h.POSTING
			FROM hitungpresensi h
			INNER JOIN karyawan k ON k.NIK=h.NIK
			INNER JOIN unitkerja uk ON uk.KODEUNIT=uk.KODEUNIT WHERE ".$where)->result();
			$data   = array();
			foreach($query as $result){
				$data[] = $result;
			}
			//$this->firephp->info($sql);
			$json	= array(
				'success'   => TRUE,
				'message'   => "Loaded data",
				'total'     => $total[0]->total,
				//'total'     => $total,
				'data'      => $data
			);
			
			return $json;
		}
	}
}
?>